#!/usr/bin/env python

#################################################################################
#
# Copyright (c) 2006 Michigan State University
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#################################################################################

#################################################################################
#
#	Author:
#		Nathan Collins <npcollins@gmail.com>
#
#################################################################################

"""Create tables necessary for the directory structure.

Creates directory tables, removing old tables if necessary.
"""

# imports
from querymgr import *
from getpass import *
from utility import *

def createTreeTable():
	"""
	Generate directory table, removing the old if necessary.
	"""

	dropQuery = "DROP TABLE IF EXISTS directoryTree;"

	dbquery(dropQuery)

	initTreeTable()

def initTreeTable():
	dbquery("CREATE TABLE directoryTree (" + \
		"recID INT AUTO_INCREMENT NOT NULL," + \
		# the hash of the parent element
		"parentHash VARCHAR(32)," + \
		# the hash of the current record
		"recHash VARCHAR(32) NOT NULL," + \
		# the hash of the link target record
		"targetHash VARCHAR(32) DEFAULT ''," + \
		# mark as either a directory, link, or record
		"isDirectory ENUM('Y','N') DEFAULT 'N'," + \
		"isLink ENUM('Y','N') DEFAULT 'N'," + \
		"isRecord ENUM('Y','N') DEFAULT 'N'," + \
		# is this a link outside the oais database
		"isExternal ENUM('Y','N') DEFAULT 'N'," + \
		# date approved by editor
		"dateApproved TIMESTAMP DEFAULT CURRENT_TIMESTAMP," + \
		# login of editor that approved addition
		"editorApproved VARCHAR(32)," + \
		# title for directory entry
		"entryTitle VARCHAR(128) NOT NULL," + \
		# description for directory entry
		"entryDescription TEXT NOT NULL," + \
		# link to article
		"entryLink TEXT NOT NULL," + \
		# keys
		"PRIMARY KEY(recID)," + \
		"UNIQUE(recHash)," + \
		"KEY(parentHash)," + \
		"KEY(targetHash)" + \
		") CHARACTER SET utf8;")

	# create the root directory
	dbquery("INSERT INTO directoryTree (" + \
		"parentHash, recHash, isDirectory) VALUES (" + \
		"'','ROOT','Y');")


def createSubmissionsTable():
	"""
	Generate submissions table, removing the old if necessary.
	"""

	dropQuery = "DROP TABLE IF EXISTS directorySubmissions;"

	dbquery(dropQuery)

	initSubmissionsTable()

def initSubmissionsTable():
	dbquery("CREATE TABLE directorySubmissions (" + \
		"recID INT AUTO_INCREMENT NOT NULL," + \
		# the hash of the parent element
		"parentHash VARCHAR(32)," + \
		# the hash of the submitted record
		"recHash VARCHAR(32) NOT NULL," + \
		# the hash of the target (for links)
		"targetHash VARCHAR(32) NOT NULL," + \
		# mark as either a directory, link, or record
		"isDirectory ENUM('Y','N') DEFAULT 'N'," + \
		"isLink ENUM('Y','N') DEFAULT 'N'," + \
		"isRecord ENUM('Y','N') DEFAULT 'N'," + \
		# is this a link outside the oais database
		"isExternal ENUM('Y','N') DEFAULT 'N'," + \
		# is this a modify/deletion of an existing part of the directory
		"isModify ENUM('Y','N') DEFAULT 'N'," + \
		# title for directory entry
		"entryTitle VARCHAR(128) NOT NULL," + \
		# description for directory entry
		"entryDescription TEXT NOT NULL DEFAULT ''," + \
		# link to article
		"entryLink TEXT NOT NULL DEFAULT ''," + \
		# optional email to auto-reply when entry is reviewed by editor
		"optionalEmail VARCHAR(128) NOT NULL," + \
		# keys
		"PRIMARY KEY(recID)," + \
		"KEY(recHash)," + \
		"KEY(parentHash)" + \
		") CHARACTER SET utf8;")


def createLoginsTable():
	"""
	Generate logins table, removing the old if necessary.
	"""

	dropQuery = "DROP TABLE IF EXISTS directoryLogins;"

	dbquery(dropQuery)

	initLoginsTable()

def initLoginsTable():
	dbquery("CREATE TABLE directoryLogins (" + \
		"recID INT AUTO_INCREMENT NOT NULL," + \
		# login name
		"loginName VARCHAR(32) NOT NULL," + \
		# password: using MD5 hash
		"loginPassword VARCHAR(32) NOT NULL," + \
		# last successful login, defaults to account creation time
		"lastLogin TIMESTAMP DEFAULT CURRENT_TIMESTAMP," + \
		# enable account: set to 'N' to disable editor account
		"enableAccount ENUM('Y','N') DEFAULT 'Y'," + \
		# super editor: can add new editors and disable accounts
		"superEditor ENUM('Y','N') DEFAULT 'N'," + \
		# session id
		"sessionID VARCHAR(32) NOT NULL," + \
		# last ip logged in from
		"lastAddress VARCHAR(128) NOT NULL DEFAULT 'never'," + \
		"PRIMARY KEY(recID)," + \
		"UNIQUE(loginName)," + \
		# actual sid is unique, but key becuase no default
		"KEY(sessionID)" + \
		") CHARACTER SET utf8;")

	# create super editor account
	print 'A super editor can add and disable other editors. You will need at least one super editor.'
	failed = 1
	while failed == 1:
		login = raw_input('Please enter the login name for the super editor: ')
		if len(login) > 32 or len(login) < 2:
			print "Failed: Login names must be at least 2 characters and no greater than 32 characters."
			continue
		passwd1 = getpass('Please enter the password for %s: ' % login)
		passwd2 = getpass('Please enter the password for %s again: ' % login)
		if passwd1 != passwd2:
			print "Failed: The two passwords you entered did not match."
			continue
		failed = 0
		# insert account into table
		loginQuery = "INSERT INTO directoryLogins (" + \
			"loginName, loginPassword, superEditor) " + \
			"VALUES ('%s','%s','Y');" % ( login, make_hash(passwd1) )
		dbquery(loginQuery)
		print "Account '%s' created." % login


def init_tables():
	initLoginsTable()
	initSubmissionsTable()
	initTreeTable()


def main():
	"""
	Main function. This is called upon execution.
	"""
	cleanOK = raw_input('Create directory tables? (type \'yes\' to create tables):\nWARNING: This will erase any existing data in the directory tables!\n')
	if cleanOK.lower() == 'yes':
		createTreeTable()
		createSubmissionsTable()
		createLoginsTable()
		print 'Any old directory tables were removed. New tables have been created.'
	else:
		print 'No tables were removed or created.'

if __name__ == '__main__':
	main()

# eof

